<?php
//https://blog.csdn.net/ice_mocha/article/details/116460057

include 'conn.php';	

require '../vendor/autoload.php';

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('../tmp/temp.Xlsx'); //载入excel表格

$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5

$lines = $highestRow - 2; 
if ($lines <= 0) {
    exit('Excel表格中没有数据');
}

$sql = "INSERT INTO `sectors_copy1` (`name`, `sex`, `mz`, `brithday`) VALUES ";

for ($row = 3; $row <= $highestRow; ++$row) {
    $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //姓名
    $chinese = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //语文
    $maths = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //数学
    $english = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //外语

    $sql .= "('$name','$chinese','$maths','$english'),";
}
$sql = rtrim($sql, ","); //去掉最后一个,号
try {
    $con->query($sql);
    echo 'OK';
} catch (Exception $e) {
    echo $e->getMessage();
}

?>